﻿using System;
using System.Configuration;
using System.Data.OleDb;
using Business.Compta;
using System.Collections.Generic;
using System.IO;

namespace DAO.Compta
{
    public class ComptaConsommationsDAO
    {
        private static string CONSO_TABLE = "CONSOMMATIONS";
        private static string CONSO_TYPE = "TYPE";
        private static string CONSO_VOLUME = "VOLUME";
        private static string CONSO_PRIX = "PRIX";
        private static string CONSO_DATE = "FDATE";
        private static string CONSO_ID = "ID";

        public static List<ComptaConsommation> getAllConsommations()
        {
            List<ComptaConsommation> _return = new List<ComptaConsommation>();

            OleDbConnection conn = MainDAO.OpenComptaConnection();


            OleDbCommand myOleDbCommand2 = new OleDbCommand("select "+
                CONSO_ID+","+CONSO_TYPE+","+CONSO_VOLUME+","+CONSO_PRIX+","+CONSO_DATE+" FROM "+CONSO_TABLE,
                conn);

            OleDbDataReader dr = myOleDbCommand2.ExecuteReader();

            int id;
            string type;
            DateTime functionnalDate;
            double prix, volume;
            while (dr.Read())
            {
                id = dr.GetInt32(0);
                type = dr.GetString(1);
                volume = dr.GetDouble(2);
                prix = dr.GetDouble(3);
                functionnalDate = dr.GetDateTime(4);

                _return.Add(new ComptaConsommation(id,type, volume, prix, functionnalDate));
            }
            dr.Close();
            conn.Close();
            return _return;
        }


        public static int InsertConsommation(ComptaConsommation conso)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into "+CONSO_TABLE+"("+
                CONSO_TYPE + "," + CONSO_VOLUME + "," + CONSO_PRIX + "," + CONSO_DATE + ") VALUES " +
                "(@type, @volume, @prix, @functionnalDate)";
            myCommand.Parameters.Add("@type", OleDbType.VarChar).Value = conso.Type;
            myCommand.Parameters.Add("@volume", OleDbType.Double).Value = conso.Volume;
            myCommand.Parameters.Add("@prix", OleDbType.Double).Value = conso.Prix;
            myCommand.Parameters.Add("@functionnalDate", OleDbType.Date).Value = conso.Date;

            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static int DeleteConsommation(ComptaStatement statement)
        {
            int _return = -1;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from "+CONSO_TABLE+" where "+CONSO_ID+" = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = statement.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }


        public static void ModifyConsommation(ComptaConsommation conso)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update "+CONSO_TABLE+" set "+
                CONSO_TYPE+" = @type, "+CONSO_VOLUME+" = @volume,"+CONSO_PRIX+" = @prix, "+CONSO_DATE+" = @date "+
                " where "+CONSO_ID+" = @id";

            myCommand.Parameters.Add("@type", OleDbType.VarChar).Value = conso.Type;
            myCommand.Parameters.Add("@volume", OleDbType.Double).Value = conso.Volume;
            myCommand.Parameters.Add("@prix", OleDbType.Double).Value = conso.Prix;
            myCommand.Parameters.Add("@date", OleDbType.Date).Value = conso.Date;

            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = conso.Id;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}
